package bcared;

import java.sql.*;
import java.util.ArrayList;

public class Customer {

	private int customerID = 0;
	private String customerName = null;
	private String customerTypeDesc = null;
	private String billingStreet = null;
	private String billingCity = null;
	private String billingState = null;
	private String billingZip = null;
	private String firstName = null;
	private String lastName = null;
	private String phone = null;
	private String email = null;
	private String position = null;
	private ArrayList<ServiceOrder> serviceOrdersArrayList = new ArrayList<ServiceOrder>();
	private ArrayList<Equipment> equipmentArrayList = new ArrayList<Equipment>();
	private ResultSet rs = null;
	private CallableStatement csmt = null;
	private PreparedStatement ps = null;
	private OraDB myDB = new OraDB();
	
	public Customer() {
		
	}

	public Customer(int customerID) throws SQLException,
			ClassNotFoundException, InstantiationException,
			IllegalAccessException {
		this.customerID = customerID;
		populate();
	}

	/**
	 * Populates all of the private variables (needs the customerID to have
	 * already been defined)
	 * 
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public void populate() throws SQLException, ClassNotFoundException,
			InstantiationException, IllegalAccessException {

		myDB.createConnection();
		String myQuery = "Select c.customerid, customername, billingstreet, billingcity, "
				+ "billingstate, billingzip, contactfirstname, contactlastname, "
				+ "contactphone, contactemail, contactposition, customerTypeDesc "
				+ "from customer c, customerType t "
				+ "where c.customertypeid = t.customertypeid and c.customerid = ?";

		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1, this.customerID);
		rs = ps.executeQuery();

		// populate the local variables
		while (rs.next()) {
		
			this.customerName = rs.getString("customername");
			this.customerTypeDesc = rs.getString("customertypedesc");
			this.billingStreet = rs.getString("billingstreet");
			this.billingCity = rs.getString("billingcity");
			this.billingState = rs.getString("billingstate");
			this.billingZip = rs.getString("billingzip");
			this.firstName = rs.getString("contactfirstname");
			this.lastName = rs.getString("contactlastname");
			this.phone = rs.getString("contactphone");
			this.email = rs.getString("contactemail");
			this.position = rs.getString("contactposition");
		}

		// populate serviceorders
		myQuery = "select SOID from serviceorders where customerID = ?";

		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1, this.customerID);
		rs = ps.executeQuery();

		while (rs.next()) {
			this.serviceOrdersArrayList.add(new ServiceOrder(rs.getInt("SOID")));
		}

		// populate equipment
		myQuery = "SELECT equipmentID FROM Equipment"
				+ " INNER JOIN Location ON Location.locationID = Equipment.locationID"
				+ " WHERE customerID = ?";
		
		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1, this.customerID);
		rs = ps.executeQuery();
		
		while (rs.next()) {
			this.equipmentArrayList.add(new Equipment(rs.getInt("equipmentID")));
		}
		
		closeConnection();

	}

//	public void setCustomerInfo(int customerid) throws ClassNotFoundException,
//			SQLException, InstantiationException, IllegalAccessException {
//
//		myDB.createConnection();
//		String myQuery = "Select c.customerid, customername, billingstreet, billingcity, "
//				+ "billingstate, billingzip, contactfirstname, contactlastname, "
//				+ "contactphone, contactemail, contactposition, customerTypeDesc "
//				+ "from customer c, customerType t "
//				+ "where c.customertypeid = t.customertypeid and c.customerid = ?";
//
//		ps = myDB.conn.prepareStatement(myQuery);
//		ps.setInt(1, customerid);
//		rs = ps.executeQuery();
//
//		while (rs.next()) {
//			this.customerID = rs.getInt("customerid");
//			this.customerName = rs.getString("customername");
//			this.customerTypeDesc = rs.getString("customertypedesc");
//			this.billingStreet = rs.getString("billingstreet");
//			this.billingCity = rs.getString("billingcity");
//			this.billingState = rs.getString("billingstate");
//			this.billingZip = rs.getString("billingzip");
//			this.firstName = rs.getString("contactfirstname");
//			this.lastName = rs.getString("contactlastname");
//			this.phone = rs.getString("contactphone");
//			this.email = rs.getString("contactemail");
//			this.position = rs.getString("contactposition");
//		}
//		
//		
//	}

	public ArrayList<ServiceOrder> getOpenServiceOrders()
			throws ClassNotFoundException, SQLException,
			InstantiationException, IllegalAccessException {

		ArrayList<ServiceOrder> openSOs = new ArrayList<ServiceOrder>();
		
		for (int i = 0; i < this.serviceOrdersArrayList.size(); i++) {
			if (this.serviceOrdersArrayList.get(i).getSoStatusID() != 1) {
				openSOs.add(this.serviceOrdersArrayList.get(i));
			}
		}

		return openSOs;
	}
	
	public ArrayList<ServiceOrder> getClosedServiceOrders()
			throws ClassNotFoundException, SQLException,
			InstantiationException, IllegalAccessException {

		ArrayList<ServiceOrder> closedSOs = new ArrayList<ServiceOrder>();

		for (int i = 0; i < this.serviceOrdersArrayList.size(); i++) {
			if (this.serviceOrdersArrayList.get(i).getSoStatusID() == 1) {
				closedSOs.add(this.serviceOrdersArrayList.get(i));
			}
		}

		return closedSOs;
	}

	public ArrayList<Equipment> getEquipment() {	
		return equipmentArrayList;
	}

	public ResultSet getOverDuePM(int customerid)
			throws ClassNotFoundException, SQLException,
			InstantiationException, IllegalAccessException {

		myDB.createConnection();
		// sysdate = 2/1/2009

		String myQuery = "select e.equipmentid, p.productname, pm.pmdescription, ppm.frequency, to_char(max(so.closeddate), 'MM/DD/YYYY') "
				+ " as LastService, to_char((max(so.closeddate) + ppm.frequency), 'MM/DD/YYYY') as ServiceDue,  "
				+ " round(((to_date('02/01/2009', 'MM/DD/YYYY')) - (max(so.closeddate) + ppm.frequency))) as DaysOverDue  "
				+ " from serviceorder so, equipment e, product p, productpm ppm, preventivemaintenance pm where so.equipmentid = e.equipmentid  "
				+ " and e.productid = p.productid and p.productid = ppm.productid and ppm.pmid = pm.pmid and so.pmid = pm.pmid and  "
				+ " e.statusid <>2 and so.equipmentid in  "
				+ "	(select e.equipmentid from equipment e, location l where e.locationid = l.locationid and l.customerid =?) group by e.equipmentid, p.productname,  "
				+ "	   pm.pmdescription, pm.pmid, ppm.frequency having  "
				+ "	      (round(((to_date('02/01/2009', 'MM/DD/YYYY')) - (max(so.closeddate) + ppm.frequency)))) > 0 and  "
				+ "	      ((max(so.closeddate)) > (max(so.opendate))) order by 7 desc, 1";

		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1, customerid);
		rs = ps.executeQuery();
		return rs;
	}

	public ResultSet getDueSoon(int customerid) throws ClassNotFoundException,
			SQLException, InstantiationException, IllegalAccessException {

		myDB.createConnection();
		// sysdate = 2/1/2009

		String myQuery = "select e.equipmentid, p.productname, pm.pmdescription, ppm.frequency, to_char(max(so.closeddate), 'MM/DD/YYYY') as LastService, "
				+ " to_char((max(so.closeddate) + ppm.frequency), 'MM/DD/YYYY') as ServiceDue,  "
				+ " round(((to_date('02/01/2009', 'MM/DD/YYYY')) - (max(so.closeddate) + ppm.frequency))*-1) as DaysUntilDue  "
				+ " from serviceorder so, equipment e, product p, productpm ppm, preventivemaintenance pm  "
				+ " where so.equipmentid = e.equipmentid and e.productid = p.productid  "
				+ " and p.productid = ppm.productid and ppm.pmid = pm.pmid and so.pmid = pm.pmid and e.statusid <>2 and  "
				+ " so.equipmentid in  "
				+ "	(select e.equipmentid from equipment e, location l where e.locationid = l.locationid and  "
				+ "      	l.customerid =?) group by e.equipmentid, p.productname, pm.pmdescription, pm.pmid, ppm.frequency having  "
				+ " 	(round(((to_date('02/01/2009', 'MM/DD/YYYY')) - (max(so.closeddate) + ppm.frequency)))) < 0 and  "
				+ " 	round(((to_date('02/01/2009', 'MM/DD/YYYY')) - (max(so.closeddate) + ppm.frequency))*-1) < 30  "
				+ "	and ((max(so.closeddate)) > (max(so.opendate))) order by 7, 1";

		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1, customerid);
		rs = ps.executeQuery();
		return rs;
	}

	public ResultSet getCustomerList() throws SQLException,
			ClassNotFoundException, InstantiationException,
			IllegalAccessException {
		// ResultSet rs = null;

		myDB.createConnection();

		String myQuery = "SELECT customerID, customerName, customerTypeDesc, billingCity || ', ' || billingState as location, contactLastName || ', ' || contactFirstName as name, contactPhone"
				+ " FROM Customer"
				+ " INNER JOIN CustomerType ON CustomerType.customerTypeID = customer.customerTypeID"
				+ " ORDER BY customerName";

		ps = myDB.conn.prepareStatement(myQuery);
		rs = ps.executeQuery();
		// closeConnection();
		return rs;
	}

	public String updateCustomers() throws ClassNotFoundException,
			SQLException, InstantiationException, IllegalAccessException {

		myDB.createConnection();
		String result = "";
		String myQuery = "call bcaredCustomerUpdateTest(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		csmt = myDB.conn.prepareCall(myQuery);
		csmt.setInt(1, customerID);
		csmt.setString(2, customerName);
		csmt.setString(3, billingStreet);
		csmt.setString(4, billingCity);
		csmt.setString(5, billingState);
		csmt.setString(6, billingZip);
		csmt.setString(7, firstName);
		csmt.setString(8, lastName);
		csmt.setString(9, phone);
		csmt.setString(10, email);
		csmt.setString(11, position);

		csmt.registerOutParameter(12, java.sql.Types.INTEGER);

		csmt.execute();

		if (csmt.getInt(12) == 1) {
			result = "Update Successful";
		} else {
			result = "Update Failed";
		}

		return result;
	}
	
	

	public int getCustomerid() {
		return customerID;
	}

	public void setCustomerid(int userid) {
		this.customerID = userid;

	}

	public String getCustomerTypeDesc() {
		if (customerTypeDesc == null)
			customerTypeDesc = "";
		return customerTypeDesc;
	}

	public void setCustomerTypeDesc(String customertypedesc) {
		this.customerTypeDesc = customertypedesc;
	}

	public String getCustomername() {
		if (customerName == null)
			customerName = "";
		return customerName;
	}

	public void setCustomername(String customername) {
		this.customerName = customername;
	}

	public String getBillingstreet() {
		if (billingStreet == null)
			billingStreet = "";
		return billingStreet;
	}

	public void setBillingstreet(String billingstreet) {
		this.billingStreet = billingstreet;
	}

	public String getBillingcity() {
		if (billingCity == null)
			billingCity = "";
		return billingCity;
	}

	public void setBillingcity(String billingcity) {
		this.billingCity = billingcity;
	}

	public String getBillingstate() {
		if (billingState == null)
			billingState = "";
		return billingState;
	}

	public void setBillingstate(String billingstate) {
		this.billingState = billingstate;
	}

	public String getBillingzip() {
		if (billingZip == null)
			billingZip = "";
		return billingZip;
	}

	public void setBillingzip(String billingzip) {
		this.billingZip = billingzip;
	}

	public String getFirstname() {
		if (firstName == null)
			firstName = "";
		return firstName;
	}

	public void setFirstname(String firstname) {
		this.firstName = firstname;
	}

	public String getLastname() {
		if (lastName == null)
			lastName = "";
		return lastName;
	}

	public void setLastname(String lastname) {
		this.lastName = lastname;
	}

	public String getPhone() {
		if (phone == null)
			phone = "";
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	public String getEmail() {
		if (email == null)
			email = "";
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public String getPosition() {
		if (position == null)
			position = "";
		return position;
	}

	public void setPosition(String position) {
		this.position = position;
	}
	
	

	public ArrayList<ServiceOrder> getServiceOrdersArrayList() {
		return serviceOrdersArrayList;
	}

	public void closeConnection() throws ClassNotFoundException, SQLException,
			InstantiationException, IllegalAccessException {
		myDB.closeConnection();
	}

}
